﻿using jiaju.Areas.Back.Models.DBModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace jiaju.Areas.Back.Models.DAL
{
    public class ProductService
    {

        public static List<Product> getProductList(int page, int limit, string name, string ZuZhuang2)
        {
            string sql = string.Format("select top({0}) Id,Name,ImgUrl,CaiZhi,ProductPrice,MarketPrice,Brand,XingHao,FengGe,ZuZhuang,Color,KuanShi,ProductTypeId,Cont,HomeFlag,(select Name  from dbo.productType where Id=ProductTypeId) as TypeName from dbo.product where Id not in (select top(({1}-1) * {0}) Id from dbo.product)", limit, page);
            if (!string.IsNullOrEmpty(name))
            {
                sql = sql + string.Format(" and Name like '%{0}%'", name);
            }
            if (!string.IsNullOrEmpty(ZuZhuang2))
            {
                sql = sql + string.Format(" and HomeFlag='{0}'", ZuZhuang2);
            }
            sql = sql + "order by Id desc";
            List<Product> list = new List<Product>();
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            while (sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Name = (string)sdr["Name"];
                string ImgUrl = (string)sdr["ImgUrl"];
                string CaiZhi = (string)sdr["CaiZhi"];
                Decimal ProductPrice = (Decimal)sdr["ProductPrice"];
                Decimal MarketPrice = (Decimal)sdr["MarketPrice"];
                string Brand = (string)sdr["Brand"];
                string XingHao = (string)sdr["XingHao"];
                string FengGe = (string)sdr["FengGe"];
                string ZuZhuang = (string)sdr["ZuZhuang"];
                string Color = (string)sdr["Color"];
                string KuanShi = (string)sdr["KuanShi"];
                int ProductTypeId = (int)sdr["ProductTypeId"];
                string HomeFlag = (string)sdr["HomeFlag"];
                string Cont = (string)sdr["Cont"];
                string TypeName = (string)sdr["TypeName"];
                Product art = new Product()
                {
                    Id = Id,
                    Name = Name,
                    ImgUrl = ImgUrl,
                    CaiZhi = CaiZhi,
                    ProductPrice = ProductPrice,
                    MarketPrice = MarketPrice,
                    Brand = Brand,
                    XingHao = XingHao,
                    FengGe = FengGe,
                    ZuZhuang = ZuZhuang,
                    Color = Color,
                    KuanShi = KuanShi,
                    ProductTypeId= ProductTypeId,
                    HomeFlag= HomeFlag,                 
                    Cont = Cont,
                    TypeName = TypeName
                };
                list.Add(art);
            }
            return list;
        }
        public static int AddProduct(Product art)
        {
            string sql = string.Format(@"INSERT INTO dbo.product
           (Name,ImgUrl,CaiZhi,ProductPrice,MarketPrice,Brand,XingHao,FengGe,ZuZhuang,Color,KuanShi,ProductTypeId,HomeFlag,Cont)
     VALUES
           ('{0}' ,'{1}','{2}' ,'{3}','{4}' ,'{5}' ,'{6}','{7}','{8}','{9}','{10}',{11},'{12}','{13}')",
           art.Name, art.ImgUrl, art.CaiZhi, art.ProductPrice, art.MarketPrice, art.Brand, art.XingHao, art.FengGe, art.ZuZhuang, art.Color, art.KuanShi, art.ProductTypeId, art.HomeFlag, art.Cont);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        public static Product GetById(string Id)
        {
            string sql = string.Format("select * from dbo.product where Id={0}", Id);
            Product product = null;
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            while (sdr.Read())
            {
                int id = (int)sdr["Id"];
                string Name = (string)sdr["Name"];
                string ImgUrl = (string)sdr["ImgUrl"];
                string CaiZhi = (string)sdr["CaiZhi"];
                Decimal ProductPrice = (Decimal)sdr["ProductPrice"];
                Decimal MarketPrice = (Decimal)sdr["MarketPrice"];
                string Brand = (string)sdr["Brand"];
                string XingHao = (string)sdr["XingHao"];
                string FengGe = (string)sdr["FengGe"];
                string ZuZhuang = (string)sdr["ZuZhuang"];
                string Color = (string)sdr["Color"];
                string KuanShi = (string)sdr["KuanShi"];
                int ProductTypeId = (int)sdr["ProductTypeId"];
                string HomeFlag = (string)sdr["HomeFlag"];
                string Cont = (string)sdr["Cont"];
                product = new Product()
                {
                    Id = id,
                    Name = Name,
                    ImgUrl = ImgUrl,
                    CaiZhi = CaiZhi,
                    ProductPrice = ProductPrice,
                    MarketPrice = MarketPrice,
                    Brand = Brand,
                    XingHao = XingHao,
                    FengGe = FengGe,
                    ZuZhuang = ZuZhuang,
                    Color = Color,
                    KuanShi = KuanShi,
                    ProductTypeId = ProductTypeId,
                    HomeFlag = HomeFlag,
                    Cont = Cont
                };
            }
            return product;
        }
        
        public static int EditProduct(Product art)
        {
            String sql = string.Format(@"update dbo.product   
                                  set Name='{0}',ImgUrl='{1}',CaiZhi='{2}',ProductPrice='{3}',MarketPrice='{4}',Brand='{5}',XingHao='{6}',
                                      FengGe='{7}',ZuZhuang='{8}',Color='{9}',KuanShi='{10}',ProductTypeId={11},HomeFlag='{12}',Cont='{14}'
                                where Id={13} ",
                             art.Name, art.ImgUrl, art.CaiZhi, art.ProductPrice, art.MarketPrice, art.Brand, art.XingHao, art.FengGe, art.ZuZhuang, art.Color, art.KuanShi, art.ProductTypeId, art.HomeFlag, art.Id, art.Cont);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        public static int DelProduct(String Id)
        {
            String sqlPhoto = String.Format("delete from dbo.picture where ProductId={0}", Id);
            int num1 = DBHelp.ExecuteNonQuery(sqlPhoto);
            String sqlRec = "delete from dbo.product where Id=" + Id;
            int num = DBHelp.ExecuteNonQuery(sqlRec);
            return num;
        }

        public static List<Product> getAllZuZhuang()
        {
            string sql = "select Id,Name,ZuZhuang from dbo.product";
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            List<Product> list = new List<Product>();
            while (sdr.Read())
            {
                int id = (int)sdr["Id"];
                string Name = (string)sdr["Name"];
                string ZuZhuang = (string)sdr["ZuZhuang"];
                Product arc = new Product()
                {
                    Id = id,
                    ZuZhuang = ZuZhuang,
                    Name = Name
                };
                list.Add(arc);
            }
            return list;
        }

    }
}